﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using BusinessLogic;
using System.Data.SqlClient;
using System.Data;
using System.Web.Security;

namespace DataAccessLogic
{
    public class RelationshipDataAccess
    {
        #region Methods
        private static Relationship BindRowToRelationship(DataRow row)
        {
            Relationship r = new Relationship();
            r.ID = DataAccess.CheckForGuidColumn(row, "ID");
            r.IsPrimary = DataAccess.CheckForBooleanColumn(row, "ISPRIMARY");
            
            Constituent relationshipConstituent = new Constituent();
            relationshipConstituent.FillByID(DataAccess.CheckForGuidColumn(row, "RELATIONSHIPCONSTITUENTID"));
            relationshipConstituent.Phone = new Phone();
            relationshipConstituent.Phone.Number = DataAccess.CheckForStringColumn(row, "NUMBER");
            relationshipConstituent.Phone.Extension = DataAccess.CheckForStringColumn(row, "EXTENSION");

            r.RelationshipConstituent = relationshipConstituent;

            Constituent reciprocalConstituent = new Constituent();
            reciprocalConstituent.FillByID(DataAccess.CheckForGuidColumn(row, "RECIPROCALCONSTITUENTID"));
            reciprocalConstituent.Phone = new Phone();
            reciprocalConstituent.Phone.Number = DataAccess.CheckForStringColumn(row, "NUMBER");
            reciprocalConstituent.Phone.Extension = DataAccess.CheckForStringColumn(row, "EXTENSION");

            r.ReciprocalConstituent = reciprocalConstituent;       
            
            RelationshipType relationshipType = new RelationshipType();
            relationshipType.FillByID(DataAccess.CheckForGuidColumn(row, "RELATIONSHIPTYPECODEID"));

            r.RelationshipTypeCode = relationshipType;

            RelationshipType reciprocalRelationshipType = new RelationshipType();
            reciprocalRelationshipType.FillByID(DataAccess.CheckForGuidColumn(row, "RECIPROCALRELATIONSHIPTYPECODEID"));

            r.ReciprocalRelationshipTypeCode = reciprocalRelationshipType;
                 
            r.DateAdded = DataAccess.CheckForDateTimeColumn(row, "DATEADDED");
            r.DateChanged = DataAccess.CheckForDateTimeColumn(row, "DATECHANGED");
            r.AddedByID = DataAccess.CheckForGuidColumn(row, "ADDEDBYID");
            r.ChangedByID = DataAccess.CheckForGuidColumn(row, "CHANGEDBYID");

            return r;
        }

        public static Guid CreateForConversion(Relationship relationship)
        {
            return Create(relationship, true);
        }

        public static Guid Create(Relationship relationship)
        {
            return Create(relationship, false);
        }

        public static Guid Create(Relationship relationship, bool isConversion)
        {
            Guid id = Guid.Empty;
            Guid providerUserKey = new Guid();

            try
            {
                providerUserKey = new Guid(Membership.GetUser().ProviderUserKey.ToString());
            }
            catch
            {
                providerUserKey = new Guid("B6E747BD-F3D5-482A-9785-9FEF877D5B6C");
            }

            if (isConversion)
            {
                providerUserKey = new Guid("c9014ff9-ee89-4192-aa6b-b0c2130557ba");
            }

            string outputParameterName = "@ID";

            if (relationship != null) 
            {
                SqlParameter p = new SqlParameter();
                p.ParameterName = outputParameterName;
                p.Direction = ParameterDirection.Output;
                p.DbType = DbType.Guid;

                List<SqlParameter> parms = new List<SqlParameter>();
                parms.Add(p);
                parms.Add(new SqlParameter("@RELATIONSHIPCONSTITUENTID", relationship.RelationshipConstituent.ID));
                parms.Add(new SqlParameter("@RECIPROCALCONSTITUENTID", relationship.ReciprocalConstituent.ID));
                parms.Add(new SqlParameter("@RELATIONSHIPTYPECODEID", relationship.RelationshipTypeCode.ID));
                parms.Add(new SqlParameter("@RECIPROCALRELATIONSHIPTYPECODEID", relationship.ReciprocalRelationshipTypeCode.ID));
                parms.Add(new SqlParameter("@ISPRIMARY", relationship.IsPrimary));
                parms.Add(new SqlParameter("@ASPNETDBUSERID", providerUserKey));

                object obj = DataAccess.Create(StoredProcedures.USP_RELATIONSHIP_CREATE, outputParameterName, parms);

                if (obj != null)
                {
                    id = new Guid(obj.ToString());
                }
            }

            return id;
        }

        public static void Delete(Guid id)
        {
            if (id != Guid.Empty)
            {
                DataAccess.Delete(StoredProcedures.USP_RELATIONSHIP_DELETEBYID, id);
            }
        }

        public static void Delete(Relationship relationship)
        {
            if (relationship != null)
            {
                Delete(relationship.ID);
            }
        }

        public static void DeleteByConstituentID(Guid constituentID)
        {
            if (constituentID != null)
            {
                List<SqlParameter> parms = new List<SqlParameter>();
                parms.Add(new SqlParameter("@CONSTITUENTID", constituentID));

                DataAccess.Delete(StoredProcedures.USP_RELATIONSHIP_DELETEBYCONSTITUENTID, parms);
            }
        }

        private static List<Relationship> Get(string storedProcedure, List<SqlParameter> parms)
        {
            List<Relationship> relationships = null;

            DataTable dt = DataAccess.Get(storedProcedure, parms);

            if(null != dt)
            {
                relationships = new List<Relationship>();

                foreach (DataRow row in dt.Rows)
                {
                    relationships.Add(BindRowToRelationship(row));
                }
            }

            return relationships;
        }
        
        public static List<Relationship> GetAllByConstituentID(Guid constituentID)
        {
            List<SqlParameter> parms = new List<SqlParameter>();
            parms.Add(new SqlParameter("@CONSTITUENTID", constituentID));
            
            return Get(StoredProcedures.USP_RELATIONSHIP_GETALLBYCONSTITUENTID, parms);
        }

        public static Relationship GetByID(Guid id)
        {
            Relationship r = null;

            if (id != Guid.Empty)
            {
                List<SqlParameter> parms = new List<SqlParameter>();
                parms.Add(new SqlParameter("@ID", id));

                List<Relationship> relationships = Get(StoredProcedures.USP_RELATIONSHIP_GETBYID, parms);

                if ((relationships != null) && (0 < relationships.Count))
                {
                    r = relationships[0];
                }
            }

            return r;
        }
        
        public static void Update(Relationship relationship)
        {
            Guid providerUserKey = new Guid(Membership.GetUser().ProviderUserKey.ToString());

            if (relationship != null)
            {
                List<SqlParameter> parms = new List<SqlParameter>();
                parms.Add(new SqlParameter("@ID", relationship.ID));
                parms.Add(new SqlParameter("@RELATIONSHIPCONSTITUENTID", relationship.RelationshipConstituent.ID));
                parms.Add(new SqlParameter("@RECIPROCALCONSTITUENTID", relationship.ReciprocalConstituent.ID));
                parms.Add(new SqlParameter("@RELATIONSHIPTYPECODEID", relationship.RelationshipTypeCode.ID));
                parms.Add(new SqlParameter("@RECIPROCALRELATIONSHIPTYPECODEID", relationship.ReciprocalRelationshipTypeCode.ID));
                parms.Add(new SqlParameter("@ISPRIMARY", relationship.IsPrimary));
                parms.Add(new SqlParameter("@ASPNETDBUSERID", providerUserKey));

                DataAccess.Update(StoredProcedures.USP_RELATIONSHIP_UPDATE, parms);
            }
        }       
        #endregion
    }
}